package com.qf.index;

import cn.hutool.core.util.NumberUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.json.JSONUtil;
import com.alibaba.fastjson.JSONObject;

import java.io.IOException;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Pattern;

public class Nsfc {
    public static void main(String[] args) throws SQLException, IOException {

        String newDataName = "scholar_25_02.";
        String doiTable = newDataName + "doi_location_20230112";
        String doiAddTable = newDataName + "doi_location_20230112_add";
        String summaryTable = newDataName + "unified_search_summary_0120_old";
        String pmArticleTable = newDataName + "spider_pubmed_vip_extend";
        String ncTextTable = "html.nc_text_table";
        String fundUnitSumTable = newDataName + "fund_unit_sum";
        String summaryFundTable = newDataName + "unified_search_summary_0120_fund";
        String univHospManualyTable = newDataName + "univ_hosp_manualy";
        String univCheckTable = "scholar_25_01.multiple_univ_check_two";
        String hospCheckTable = "scholar_25_01.multiple_hosp_check_two";
        String standardUnitSumTable = newDataName + "basic_standard_unit_sum";

        //        String limit = "";
        String limit = " limit 100";

        // 获取实际的国家自然科学基金资助的机构清单，保存进fundUnitSumTable表。
//        insertFundUnitSumTable(summaryTable, "id", "year`,`pmId`,`fund_list`,`ut`,`c1_au`,`rp_au", fundUnitSumTable, summaryFundTable);


        // 获取人工核实后的机构信息表（univHospManualyTable），规范的时间大约在2025.03.14之前的一周左右。
        Map<String, String> infoAndUnitMap = getInfoAndUnitMap(standardUnitSumTable, "id", "info`,`unit");
        System.out.println("infoAndUnitMap.size(): " + infoAndUnitMap.size());


        updateFundUnitSumTable(fundUnitSumTable, "id", "info", infoAndUnitMap);

// main
    }

    public static void updateFundUnitSumTable(String fundUnitSumTable, String autoId, String field, Map<String, String> infoAndUnitMap) throws SQLException {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        int onePageNum = 10000;
        int tableMax = Db.use().queryNumber("select max(" + autoId + ") from " + fundUnitSumTable).intValue();
        if (tableMax > 0) {
            Collection<Integer> tableList = new ArrayList<>();
            NumberUtil.appendRange(0, tableMax + onePageNum, onePageNum, tableList);
            for (Integer startId : tableList) {
                List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + field + "` from " + fundUnitSumTable + " where " + autoId + " between " + startId + " and " + (startId + onePageNum - 1));
                System.out.println("\033[31;1m" + format.format(new Date()) + " 读表现在开始：" + startId + " 表名：" + fundUnitSumTable + Utils.printMemoryStr() + "\033[0m");
                for (Entity entry : tableData) {
                    String info = Utils.getStrField(entry, "info");
                    Integer id = entry.getInt(autoId);
                    if (null != id && id > 0 && !info.isEmpty() && infoAndUnitMap.containsKey(info)) {
                        try {
                            Db.use().update(Entity.create().set("unit", infoAndUnitMap.get(info)), Entity.create(fundUnitSumTable).set(autoId, id));
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }
                }
            }
        }
    }

    public static void insertFundUnitSumTable(String summaryTable, String autoId, String field, String fundUnitSumTable, String summaryFundTable) throws SQLException {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Db.use().execute("truncate table " + fundUnitSumTable);
        Db.use().execute("truncate table " + summaryFundTable);
        String limit = "";
//        String limit = " limit 1";
        int onePageNum = 1000;
        Set<String> hospSet = uSet.getHospSet();
        Set<String> removeSet = uSet.getRemoveSet();
        Set<String> nameSet = cList.getProvName();
        Map<String, Integer> fundAndSumMap = new HashMap<>();
        int tableMax = Db.use().queryNumber("select max(" + autoId + ") from " + summaryTable).intValue();
        if (tableMax > 0) {
            Collection<Integer> tableList = new ArrayList<>();
            NumberUtil.appendRange(0, tableMax + onePageNum, onePageNum, tableList);
            tableList.forEach(startId -> {
                try {
                    List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + field + "` from " + summaryTable + " where " + autoId + " between " + startId + " and " + (startId + onePageNum - 1) + limit);
                    System.out.println("\033[31;1m" + format.format(new Date()) + " 读表现在开始：" + startId + " 表名：" + summaryTable + Utils.printMemoryStr() + "\033[0m");
                    for (Entity entry : tableData) {
                        int year = Utils.getIntField(entry, "year");
                        int pmId = Utils.getIntField(entry, "pmId");
                        String funds = Utils.getStrField(entry, "fund_list");
                        String ut = Utils.getStrField(entry, "ut");
                        String f = funds.toLowerCase();
//                        if (year > 2017 && funds.contains("\"]") && (pmId > 0 || !ut.isEmpty()) && (f.contains("china ") || f.contains(" china") || f.contains("(nsfc)") || funds.contains("NSFC"))) {
//                            List<String> fundList = JSONObject.parseArray(funds, String.class);
//                            boolean bool = false;
//                            for (String fund : fundList) {
//                                if (isNsfc(fund)) {
//                                    bool = true;
//                                    break;
//                                }
//                            }
//                            if (bool) {
//                                String c1Au = Utils.getStrField(entry, "c1_au");
//                                String rpAu = Utils.getStrField(entry, "rp_au");
//                                List<En.C1Auth> c1List = (c1Au.contains("sort")) ? JSONObject.parseArray(c1Au, En.C1Auth.class) : new ArrayList<>();
//                                List<En.C1Auth> rpChangeC1 = (rpAu.contains("sort")) ? JSONObject.parseArray(rpAu, En.C1Auth.class) : new ArrayList<>();
//                                if (ChinaHosp.isChinaHosp(c1List, nameSet, hospSet, removeSet) || ChinaHosp.isChinaHosp(rpChangeC1, nameSet, hospSet, removeSet)) {
//                                    try {
//                                        Db.use().insert(Entity.create(summaryFundTable).set("year", year).set("pmId", pmId).set("fund_list", funds).set("c1_au", c1Au).set("rp_au", rpAu).set("ut", ut));
//                                    } catch (SQLException e) {
//                                        e.printStackTrace();
//                                    }
//                                    for (En.C1Auth c1Auth : c1List) {
//                                        Set<String> unitSet = c1Auth.getUnitSet();
//                                        String unit = CheckUnit.listToUnit(new ArrayList<>(unitSet));
//                                        fundAndSumMap.compute(unit.toLowerCase().trim(), (key, value) -> value != null ? (value + 1) : 1);
//                                    }
//                                }
//                            }
//                        }
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            });
        }
        fundAndSumMap.keySet().parallelStream().forEach(fund -> {
            try {
                Db.use().insert(Entity.create(fundUnitSumTable).set("info", fund).set("sum", fundAndSumMap.get(fund)));
            } catch (SQLException e) {
                e.printStackTrace();
            }
        });
    }

//    public static boolean isNsfc(String info) {
//        String l = info.toLowerCase().trim();
//        if (l.contains("china ") || l.contains(" china") || info.contains("NSFC") || l.contains("(nsfc)")) {
//            if (l.contains("province") || l.contains("research") || l.contains("provincial") || l.contains("technology") || l.contains("social") || l.contains("education") || l.contains("scholarship") ||
//                    l.contains("973") || l.contains("111") || l.contains("863") || l.contains("postdoctoral") || l.contains("university") || l.contains("universities") || l.contains("key r&d")) {
//                return false;
//            }
//            if (l.contains("(nsfc)") || Pattern.compile("nati[a-z ]+natu[a-z ]+scie[a-z ]+fund[a-z ]+|nati[a-z ]+natu[a-z ]+scie[a-z ]+foun[a-z ]+").matcher(l).find() || info.contains("NSF of China") || info.contains("NSFC")) {
//                return true;
//            }
//            return l.contains("national science foundation of china") || l.contains("national natural science foun-dation of china") || l.contains("national natural sci-ence foundation of china") || l.contains("nature science foundation of china") ||
//                    l.contains("national natural sci- ence foundation of china") || l.contains("national natural foundation of china") || l.contains("na-tional natural science foundation of china") || l.contains("national nat-ural science foundation of china") ||
//                    l.contains("national natural science of china") || l.contains("na- tional natural science foundation of china") || l.contains("national scientific foundation of china") || l.contains("natural science foundations of china") ||
//                    l.contains("china natural science foundation") || l.contains("national nat- ural science foundation of china") || l.contains("national science fund of china") || l.contains("natural sciences foundation of china") ||
//                    l.contains("china national science foundation") || l.contains("nsf china") || l.contains("china nsf") || l.contains("natural science fund of china") || l.contains("national natu-ral science foundation of china") ||
//                    l.contains("national sciences foundation of china") || l.contains("natural national science foundation of china") || l.contains("national foundation of china") || l.contains("national science funds of china") ||
//                    l.contains("national science foundation china") || "science foundation of china".equals(l) || "national science foundation (nsf)".equals(l);
//        }
//        return false;
//    }

    public static Map<String, String> getInfoAndUnitMap(String standardUnitSumTable, String autoId, String field) throws SQLException {
        int onePageNum = 1000;
        Map<String, String> infoAndUnitMap = new HashMap<>();
        int tableMax = Db.use().queryNumber("select max(" + autoId + ") from " + standardUnitSumTable).intValue();
        if (tableMax > 0) {
            Collection<Integer> tableList = new ArrayList<>();
            NumberUtil.appendRange(0, tableMax + onePageNum, onePageNum, tableList);
            for (Integer startId : tableList) {
                List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + field + "` from " + standardUnitSumTable + " where " + autoId + " between " + startId + " and " + (startId + onePageNum - 1));
                for (Entity entry : tableData) {
                    String info = Utils.getStrField(entry, "info");
                    String unit = Utils.getStrField(entry, "unit");
                    if (info.length() > 3 && unit.length() > 6) {
                        infoAndUnitMap.put(info, unit);
                    }
                }
            }
        }
        return infoAndUnitMap;
    }


}
